package com.thecafetechno.main;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;

import com.thecafetechno.university.Student;
import com.thecafetechno.util.HibernateUtil;

public class DataSelectionHQLExampleWithRestriction {
	public static void main(String[] ar) {
		DataPopulator.populateData();
		System.out.println();
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		Transaction transaction = session.beginTransaction();
		// this find all student objects, where name='Saurabh'
		List<Student> studentList = session.createQuery(
				"from Student student where student.name='Saurabh'").list();
		transaction.commit();
		System.out
				.println("Result for : \"from Student student where student.name='Saurabh'\" ");
		for (Student student : studentList) {
			System.out.println(student);
		}

		System.out.println();
		session = HibernateUtil.getSessionFactory().openSession();
		transaction = session.beginTransaction();
		// this find students, where marks are less than 75
		studentList = session.createQuery(
				"from Student student where student.marks<75").list();
		transaction.commit();
		System.out
				.println("Result for : \"from Student student where student.marks<75\" ");
		for (Student student : studentList) {
			System.out.println(student);
		}

		System.out.println();
		session = HibernateUtil.getSessionFactory().openSession();
		transaction = session.beginTransaction();
		// this find students, where marks are between than 75 and 90
		studentList = session.createQuery(
				"from Student student where student.marks between 75 and 90")
				.list();
		transaction.commit();
		System.out
				.println("Result for : \"from Student student where student.marks between 75 and 90\" ");
		for (Student student : studentList) {
			System.out.println(student);
		}

		System.out.println();
		session = HibernateUtil.getSessionFactory().openSession();
		transaction = session.beginTransaction();
		// this find students, whose names are saurabh, anu or pushpi
		studentList = session
				.createQuery(
						"from Student student where student.name in ('saurabh','anu','pushpi')")
				.list();
		transaction.commit();
		System.out
				.println("Result for : \"from Student student where student.name in ('saurabh','anu','pushpi')\" ");
		for (Student student : studentList) {
			System.out.println(student);
		}

		System.out.println();
		session = HibernateUtil.getSessionFactory().openSession();
		transaction = session.beginTransaction();
		// this find students, who have null extra subject value
		// note we use here 'is null' in expression and not
		// '= null'. This is beacuse in sql 'null = null' is null
		studentList = session.createQuery(
				"from Student student where student.extraSubject is null")
				.list();
		transaction.commit();
		System.out
				.println("Result for : \"from Student student where student.extraSubject is null\" ");
		for (Student student : studentList) {
			System.out.println(student);
		}

		System.out.println();
		session = HibernateUtil.getSessionFactory().openSession();
		transaction = session.beginTransaction();
		// this find students, who have name like *ur*
		studentList = session.createQuery(
				"from Student student where student.name like '%ur%'").list();
		transaction.commit();
		System.out
				.println("Result for : \"from Student student where studentname like '%ur%'\" ");
		for (Student student : studentList) {
			System.out.println(student);
		}

		System.out.println();
		session = HibernateUtil.getSessionFactory().openSession();
		transaction = session.beginTransaction();
		// this find students, who have name not like *ur*
		// % matches any number of characters
		// _ matches single characher
		// to escape % or _, we can define escape character.
		// example: "from Student student where student.name like '\%sa%'
		// escape='\'"
		studentList = session.createQuery(
				"from Student student where student.name not like '%ur%'")
				.list();
		transaction.commit();
		System.out
				.println("Result for : \"from Student student where studentname not like '%ur%'\" ");
		for (Student student : studentList) {
			System.out.println(student);
		}

		System.out.println();
		session = HibernateUtil.getSessionFactory().openSession();
		transaction = session.beginTransaction();
		// Logical operators can be used to combine expressions
		studentList = session
				.createQuery(
						"from Student student where (student.name in ('Saurabh','Pooja') and student.extraSubject is null) or student.marks<75")
				.list();
		transaction.commit();
		System.out
				.println("from Student student where (student.name in ('Saurabh','Pooja') and student.extraSubject is null) or student.marks<75");
		for (Student student : studentList) {
			System.out.println(student);
		}

	}
}
